#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
my ($cmd);
use ensembl_parameters;
use db_parameters;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_taxon_interpro_matches

	Description:
		Obtain ENSEMBL interpro data from sanger database
	Populates DB tables:
		domains.interpro_data

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE


#_________________________________________________________________________________________

#	retrieve_ensembl_taxon_prot_interpro_matches

#		retrieve data from ENSEMBL

#_________________________________________________________________________________________
sub retrieve_ensembl_taxon_prot_interpro_matches($)
{
	my ($taxon) = @_;
	my $ens_db_name = $taxon->[ENS_TAXON] .'_core_'.$taxon->[ENS_VERSION];
	my $dbh = DBI->connect("dbi:mysql:$ens_db_name:$ens_db_host", "$ens_db_user", '',
							{
								RaiseError => 1,
								AutoCommit => 1,
								PrintError => 0
							}
						)
	or die "Database connection not made: DBI::errstr()";
	my $array = $dbh->selectall_arrayref(<<'PL/SQLCMD');
	SELECT
		an.logic_name,
		annot.hit_id,
		annot.evalue,
		ensp.stable_id,
		i.interpro_ac,
		annot.seq_start,
		annot.seq_end
	FROM
		analysis an,
		interpro i,
		protein_feature annot,
		transcript cdna,
		translation prot,
		gene_stable_id ensg,
		translation_stable_id ensp
	WHERE
		annot.hit_id = i.id                         AND
		cdna.gene_id = ensg.gene_id                 AND
		cdna.transcript_id = prot.transcript_id     AND
		prot.translation_id = annot.translation_id  AND
		ensp.translation_id = annot.translation_id  AND
		an.analysis_id = annot.analysis_id          AND
		an.logic_name <> 'scanprosite'              AND
		an.logic_name <> 'pfscan'	  			    AND
		an.logic_name <> 'Prints'
	ORDER BY
		ensg.stable_id,
		ensp.stable_id;
PL/SQLCMD
	$dbh->disconnect;
	return $array;
}







#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

# Main logic

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888


#
#	Get DB parameters
#
my @db_param = get_ensembl_taxon_data();
my $dbh = connect_to_panda();
$dbh->do("TRUNCATE taxon.prot_interpro_matches");
$dbh->do("VACUUM ANALYSE taxon.prot_interpro_matches");
for my $taxon_data(@db_param)
{

	#
	#	Retrieve from ENSEMBL
	#
	print STDERR "\t$taxon_data->[TAXON] interpro matches...\n";
	print STDERR "\t\tRetrieving from Ensembl...\n";
	my $interpro = retrieve_ensembl_taxon_prot_interpro_matches($taxon_data);



	#
	#	Copy to panda
	#
	print STDERR "\t\tCopying ".scalar(@$interpro)." interpro matches to panda\n";
	$dbh->do(<<"PL/SQLCMD");
		COPY taxon.prot_interpro_matches
				(method, match_ac, e_value,
					prot_id, interpro_ac, pos_from, pos_to) FROM STDIN;
PL/SQLCMD
	for my $hit(@$interpro)
	{
		$dbh->func(join ("\t",  @$hit)."\n", 'putline');
	}
	$dbh->func("\\.\n", 'putline');
	$dbh->func('endcopy');

}


$dbh->disconnect;
print STDERR "\tCompleted\n";





